import pymysql
import re


class WordSql():
    def __init__(self, host, user, password, port, dbName, charset):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.dbName = dbName
        self.charset = charset
        self.conn = None
        self.cursor = None
        self.dictcursor = None

    def connect(self):#登录数据库
        self.conn = pymysql.connect(host=self.host  # 连接名称，默认127.0.0.1
                                    , user=self.user  # 用户名
                                    , password=self.password  # 密码
                                    , port=self.port  # 端口，默认为3306
                                    , db=self.dbName  # 数据库名称
                                    , charset=self.charset  # 字符编码
                                    )
        self.cursor = self.conn.cursor()
        self.dictcursor = self.conn.cursor(pymysql.cursors.DictCursor)

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_one(self, sql):
        res = None
        try:
            self.connect()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except:
            print("查询失败")
        return res

    def get_onedict(self, sql):
        res = None
        try:
            self.connect()
            self.dictcursor.execute(sql)
            res = self.dictcursor.fetchone()
            self.close()
        except:
            print("查询失败")
        return res
    def get_alldict(self):
        res = None
        try:
            self.connect()
            self.dictcursor.execute(sql)
            res = self.dictcursor.fetchall()
            self.close()
        except:
            print("查询失败")
        return res

    def get_all(self, sql):
        res = ()
        try:
            self.connect()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except:
            print("查询失败")

        return res

    def create(self, sql):
        return self._edit(sql)

    def createone(self, sql, data):
        try:
            self.connect()
            sql = self.cursor.mogrify(sql, data)
            sql = re.sub('\'', "", sql)
            self.close()
            return self._edit(sql)
        except:
            print("创建失败")

    def insert(self, sql):
        return self._edit(sql)

    # def insertcoursers(self, sql, tile, data):
    #     try:
    #         self.connect()
    #         sql = self.cursor.mogrify(sql, tile)
    #         sql = re.sub('\'', "", sql)
    #         sql += "values(" + "%s," * (len(data[0]) - 1) + "%s)"
    #         self.cursor.executemany(sql, data)
    #         self.conn.commit()
    #         self.close()
    #     except:
    #         self.conn.rollback()
    #         print("课程插入失败")
    def insertcoursers(self, sql, tile, data):
        sql = self.cursor.mogrify(sql, tile)
        sql = re.sub('\'', "", sql)
        sql += "values(" + "%s," * (len(data[0]) - 1) + "%s)"
        self.cursor.executemany(sql, data)

    def update(self, sql):
        return self._edit(sql)

    def delete(self, sql):
        return self._edit(sql)

    def _edit(self, sql):
        count = 0
        try:
            self.connect()
            count = self.cursor.execute(sql)
            self.conn.commit()
            self.close()
        except:
            print("事物提交失败")
            self.conn.rollback()
        return count


if __name__ == '__main__':
    wdsql = WordSql('127.0.0.1', 'root', '123456', 3306, 'word', 'utf8')
    stname = ['通识课', '通识选修课', '学科基础课', '学科专业基础课', '专业选修课', '集中性实践', '第二课堂']
    sql = "create table if not exists user (userid int(10) auto_increment primary key,name char(20) not null," \
          "password char(30) default '123456',major char(30))"
    print(wdsql.get_all(sql))
    print(wdsql.create(sql))
